﻿using MySqlConnector;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 数据库批量数据的存储
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        #region 变量
        static readonly string mysqlPath = "server=localhost;port=3306;uid=root;pwd=root;database=test;AllowLoadLocalInfile=true";//数据库地址
        static readonly MySqlConnection conn = new MySqlConnection(mysqlPath);//数据库连接
        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            this.ChangGuiChaRu();
         }
        /// <summary>
        /// 常规插入数据的方法
        /// </summary>
        private void ChangGuiChaRu()
        {
            try
            {
                MySqlCommand mySqlCommand = null;
                conn.Open();//打开数据库
                Stopwatch sw = new Stopwatch(); //计时器
                sw.Start();//开始计时
                for (int i = 0; i < 50000; i++)
                {
                    mySqlCommand = new MySqlCommand($"insert into 手机信息(品牌,型号,价格,进货时间,保修时间) " +
                        $"values('小米','xiaomi13','{i}元','{DateTime.Now.ToString()}','3年')", conn);
                    mySqlCommand.ExecuteNonQuery();
                }
                sw.Stop();//停止计时
                MessageBox.Show($"用时：{((float)sw.ElapsedMilliseconds / 1000).ToString()}秒");
                conn.Close();
            }
            catch (Exception ex)
            {
                conn.Close();//关闭数据库
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                conn.Close(); //关闭数据库
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.ShiWuChaRu();
        }
        /// <summary>
        /// 事务插入的方法
        /// </summary>
        private void ShiWuChaRu()
        {
            try
            {
                MySqlCommand mySqlCommand = null;
                conn.Open();//打开数据库
                MySqlTransaction mySqlTransaction = conn.BeginTransaction(); //开始事务
                Stopwatch sw = new Stopwatch(); //计时器
                sw.Start();//开始计时
                for (int i = 0; i < 50000; i++)
                {
                    mySqlCommand = new MySqlCommand($"insert into 手机信息(品牌,型号,价格,进货时间,保修时间) " +
                        $"values('小米','xiaomi13','{i}元','{DateTime.Now.ToString()}','3年')", conn, mySqlTransaction);//增加事务
                    mySqlCommand.ExecuteNonQuery();
                }
                mySqlTransaction.Commit(); //数据进行一次性提交
                sw.Stop();//停止计时
                MessageBox.Show($"用时：{((float)sw.ElapsedMilliseconds / 1000).ToString()}秒");
                conn.Close();
            }
            catch (Exception ex)
            {
                conn.Close();//关闭数据库
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                conn.Close(); //关闭数据库
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.PiLiangChaRu();
        }
        /// <summary>
        /// 批量插入的方法
        /// </summary>
        /// <exception cref="NotImplementedException"></exception>
        private void PiLiangChaRu()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("品牌");
            dt.Columns.Add("型号");
            dt.Columns.Add("价格");
            dt.Columns.Add("进货时间");
            dt.Columns.Add("维修时间");
            for (int i = 0; i < 50000; i++)
            {
                dt.Rows.Add(i,"小米","xiaomi13",$"{i}元",DateTime.Now.ToString(),"3年");
            }
            Stopwatch sw = new Stopwatch(); //计时器
            sw.Start();//开始计时
            this.SqlBulkCopyByDataTable("手机信息",dt);
            sw.Stop();
            MessageBox.Show($"用时：{((float)sw.ElapsedMilliseconds / 1000).ToString()}秒");
        }

        private void SqlBulkCopyByDataTable(string tableName, DataTable dataTable)
        {
            try
            {
                conn.Open();
                MySqlBulkCopy mySqlBulkCopy = new MySqlBulkCopy(conn);
                mySqlBulkCopy.DestinationTableName = tableName; //目标表格
                mySqlBulkCopy.WriteToServer(dataTable);
                conn.Close();
            }
            catch (Exception)
            {
                conn.Close();
                throw;
            }
            finally { conn.Close(); }
        }
    }
}
